package dao;

import db.DbUtil;
import entity.Goods;
import entity.Order;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 蒋银辉
 * @version 1.0
 */
public class OrderDao {

    public int addOrder(Order order) {
        int ret = DbUtil.getInstance().execute("insert into order_detail(order_id,user_id,goods_id,goods_name,goods_image,note,price,qty,orderSum,address,state,create_time,update_time) values(?,?,?,?,?,?,?,?,?,?,?,?,?)", new Object[]{order.getOrderId(),order.getUserId(),order.getGoodsId(),order.getGoodsName(),order.getGoodsImage(),order.getNote(),order.getPrice(),order.getQty(),order.getOrderSum(),order.getAddress(),order.getState(),order.getCreateTime(),order.getUpdateTime()});
        return ret;
    }

    public List<Order> selectMyOrder(Integer userId) {
        List<Order> list=DbUtil.getInstance().queryList("select * from order_detail where user_id=?",new Object[]{userId},Order.class);
        return list;
    }

    public List<Order> queryOrderPage(int start, int size, int userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select * from order_detail where user_id = ? limit ?,?";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userId);
            ps.setObject(2,start);
            ps.setObject(3,size);

            ResultSet rs = ps.executeQuery();
            List<Order> list = new ArrayList<>();
            while(rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getString("order_id"));
                order.setUserId(rs.getInt("user_id"));
                order.setGoodsId(rs.getInt("goods_id"));
                order.setGoodsName(rs.getString("goods_name"));
                order.setGoodsImage(rs.getString("goods_image"));
                order.setNote(rs.getString("note"));
                order.setPrice(rs.getBigDecimal("price"));
                order.setQty(rs.getInt("qty"));
                order.setAddress(rs.getString("address"));
                order.setState(rs.getInt("state"));
                if (rs.getTimestamp("create_time") != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    order.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
                }
                if (rs.getTimestamp("update_time") != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    order.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
                }
                list.add(order);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return null;
    }

    public int total_order( int userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select count(*) from order_detail where user_id = "+ userId;
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return 0;
    }

    public int deleteToAddress(String orderId) {
        int ret = DbUtil.getInstance().execute("delete from order_detail where order_id=?", new Object[]{orderId});
        return ret;
    }

    public List<Order> queryOrderPage(int start, int size, String condition, ArrayList<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select * from order_detail where "+ condition +" limit ?,?";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ps.setObject(parmas.size()+1,start);
            ps.setObject(parmas.size()+2,size);

            ResultSet rs = ps.executeQuery();
            List<Order> list = new ArrayList<>();
            while(rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getString("order_id"));
                order.setUserId(rs.getInt("user_id"));
                order.setGoodsId(rs.getInt("goods_id"));
                order.setGoodsName(rs.getString("goods_name"));
                order.setGoodsImage(rs.getString("goods_image"));
                order.setNote(rs.getString("note"));
                order.setPrice(rs.getBigDecimal("price"));
                order.setQty(rs.getInt("qty"));
                order.setOrderSum(rs.getBigDecimal("orderSum"));
                order.setAddress(rs.getString("address"));
                order.setState(rs.getInt("state"));
                if (rs.getTimestamp("create_time") != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    order.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
                }
                if (rs.getTimestamp("update_time") != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    order.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
                }
                list.add(order);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return null;
    }

    public int deleteOrder(Integer id) {
        int ret = DbUtil.getInstance().execute("delete from order_detail where order_id=?", new Object[]{id});
        return ret;
    }

    public int updateAOrder(String userId, String goodsId, String goodsName,String goodsImage,String note, String price, String qty, String orderSum, String address, String state, String orderId) {
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String updateTime = currentTime.format(formatter);
        int ret = DbUtil.getInstance().execute("update order_detail set user_id=?,goods_id=?,goods_name=?,goods_image=?,note=?,price=?,qty=?,orderSum=?,address=?,state=?,update_time=? where order_id=?", new Object[]{userId,goodsId,goodsName,goodsImage,note,price,qty,orderSum,address,state,updateTime,orderId});
        return ret;
    }

    private Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/shopping?useUnicode=true&characterEncoding=utf8",
                    "root", "123456");
            return conn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void closeConnection(Statement stmt, Connection conn) {
        try {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int total_orders(String condition, ArrayList<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select count(*) from order_detail where "+ condition;
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return 0;
    }

}